﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace Database
{
    public class DBConnect
    {
        private MySqlConnection connection;
        private string server;
        private string database;
        private string uid;
        private string password;
        private bool connectionOpen = false;

        #region Connection
        //Constructor
        public DBConnect(string server, string database, string uid, string password)
        {
            Initialize(server, database, uid, password);
        }
        //Initialize values
        /// <summary>
        /// 
        /// </summary>
        /// <param name="server"></param>
        /// <param name="database"></param>
        /// <param name="uid"></param>
        /// <param name="password"></param>
        private void Initialize(string server, string database, string uid, string password)
        { 
            this.server = server;
            this.database = database;
            this.uid = uid;
            this.password = password;
            string connectionString;
            connectionString = "SERVER=" + server + ";" + "DATABASE=" + database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

            connection = new MySqlConnection(connectionString);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public bool TestConnectivity()
        {
            try
            {
                connection.Open();
                connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Open Database Connection
        /// </summary>
        /// <returns>true if successful/already open</returns>
        public bool OpenConnection()
        {
            try
            {
                if (connectionOpen == false)
                {
                    connection.Open();
                    connectionOpen = true;
                }
                return true;

            }
            catch (MySqlException)
            {
                //When handling errors, you can your application's response based 
                //on the error number.
                //The two most common error numbers when connecting are as follows:
                //0: Cannot connect to server.
                ////1045: Invalid user name and/or password.
                //throw ex;
                //switch (ex.Number)
                //{
                //    case 40:
                //        Console.WriteLine("Cannot connect to server.  Contact administrator");
                //        break;

                //    case 1045:
                //        Console.WriteLine("Invalid username/password, please try again");
                //        break;
                //    default:
                //        Console.WriteLine("Error: " + ex.Number + " " + ex.Message);
                //        break;
                //}
                return false;
            }
        }

        /// <summary>
        /// Close Database Connection
        /// </summary>
        /// <returns>true if successful/already closed</returns>
        public bool CloseConnection()
        {
            try
            {
                if (connectionOpen == true)
                {
                    connection.Close();
                    connectionOpen = false;
                }
                return true;
            }
            catch (MySqlException)
            {
                return false;
            }
        }
        //Send the created query
        private void SendQuery(string query)
        {
            if (this.connectionOpen == true)
            {
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, connection);

                if(cmd.Connection.State == System.Data.ConnectionState.Closed)
                    cmd.Connection.Open();

                //Execute command
                cmd.ExecuteNonQuery();

            }
        }
        //Send the created query and return last inserted ID
        private int SendQueryRet(string query)
        {
            int lastId=-1;

            if (this.connectionOpen == true)
            {
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, connection);

                if (cmd.Connection.State == System.Data.ConnectionState.Closed)
                    cmd.Connection.Open();

                //Execute command
                cmd.ExecuteNonQuery();

                lastId = (int)cmd.LastInsertedId;

            }

            return lastId;
        }
        #endregion

        #region GetTables
        public List<string>[] GetTables()
        {
            string query = "SHOW TABLES";

            //Create a list to store the result
            List<string>[] tables = new List<string>[1];
            tables[0] = new List<string>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    tables[0].Add(dataReader["Tables_in_invit"] + "");
                    //list[1].Add(dataReader["test2"] + "");
                    //list[2].Add(dataReader["age"] + "");
                }

                //close Data Reader
                dataReader.Close();


                //return list to be displayed
                return tables;
            }
            else
            {
                return tables;
            }
        }
        #endregion

        #region Addition
        public int InsertIntoAddition(List<Addition> list)
        {
            if (list == null || list.Count == 0)
                return -1;

            string query;
            bool first = true;


            query = "INSERT INTO addition (text, date, device_ID, additionType_ID) VALUES ";

            foreach (Addition addition in list)
            {
                if (first)
                {
                    first = false;
                }
                else
                {
                    query += ",";
                }
                query += "('" + addition.Text + "', '" + ConvertToDateTimeString(addition.Date) + "', '" + addition.DeviceId + "', '" + addition.AdditionTypeId + "')";
            }

            query += ";";
            int lastId = SendQueryRet(query);
            return lastId;
        }
        public int InsertIntoAddition(Addition addition)
        {
            string query;

            query = "INSERT INTO addition (text, date, device_ID, additionType_ID) VALUES ";

            query += "('" + addition.Text + "', '" + ConvertToDateTimeString(addition.Date) + "', '" + addition.DeviceId + "', '" + addition.AdditionTypeId + "')";


            query += ";";
            int lastId = SendQueryRet(query);
            return lastId;
        }
        public void DeleteAllAddition()
        {
            string query = "DELETE FROM addition;";
            SendQuery(query);
        }
        public void DeleteRangeAddition(int startID, int endID) 
        {
            string query = " DELETE FROM addition WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            SendQuery(query);
        }
        public List<Addition> GetRangeAddition(int startID, int endID)
        {
            string query = "SELECT * FROM addition WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            List<Addition> additions = new List<Addition>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Addition element
                    Addition addition = new Addition();
                    //Write the information in the temporary element
                    addition.Id = Convert.ToInt32(dataReader["ID"]);
                    addition.Text = Convert.ToString(dataReader["text"]);
                    addition.Date = Convert.ToDateTime(dataReader["date"]);
                    addition.DeviceId = Convert.ToInt32(dataReader["device_ID"]);
                    addition.AdditionTypeId = Convert.ToInt32(dataReader["additionType_ID"]);
                    //insert the temporary element into the list
                    additions.Add(addition);
                }

                //close Data Reader
                dataReader.Close();


                //return list to be displayed
                return additions;
            }
            else
            {
                return additions;
            }
        }
        public List<Addition> GetAllAddition() 
        {
            string query = "SELECT * FROM addition";
            List<Addition> additions = new List<Addition>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Addition element
                    Addition addition = new Addition();
                    //Write the information in the temporary element
                    addition.Id = Convert.ToInt32(dataReader["ID"]);
                    addition.Text = Convert.ToString(dataReader["text"]);
                    addition.Date = Convert.ToDateTime(dataReader["date"]);
                    addition.DeviceId = Convert.ToInt32(dataReader["device_ID"]);
                    addition.AdditionTypeId = Convert.ToInt32(dataReader["additionType_ID"]);
                    //insert the temporary element into the list
                    additions.Add(addition);
                }

                //close Data Reader
                dataReader.Close();


                //return list to be displayed
                return additions;
            }
            else
            {
                return additions;
            }
        }
        public int CountAddition()
        {
            string query = "SELECT Count(*) FROM addition";
            int Count = -1;

            //Open Connection
            if (this.connectionOpen == true)
            {
                //Create Mysql Command
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //ExecuteScalar will return one value
                Count = int.Parse(cmd.ExecuteScalar() + "");

                return Count;
            }
            else
            {
                return Count;
            }
        }
        public void UpdateAddition(int id, Addition addition)
        {
            string query = "UPDATE addition SET text='" + addition.Text + "', date='" + ConvertToDateTimeString(addition.Date) + "', device_ID='" + addition.DeviceId + "', additionType_ID='" + addition.AdditionTypeId + "' WHERE ID='" + id + "';";
            SendQuery(query);
        }
        #endregion

        #region AdditionType
        public int InsertIntoAdditionType(AdditionType element)
        {
            string query;

            query = "INSERT INTO additiontype (type) VALUES ";

            query += "('" + element.Type + "')";


            query += ";";

            int lastId = SendQueryRet(query);
            return lastId;
        }
        public void DeleteAllAdditionType()
        {
            string query = "DELETE FROM additiontype;";
            SendQuery(query);
        }
        public void DeleteRangeAdditionType(int startID, int endID)
        {
            string query = " DELETE FROM additiontype WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            SendQuery(query);
        }
        public List<AdditionType> GetRangeAdditionType(int startID, int endID)
        {
            string query = "SELECT * FROM additiontype WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            List<AdditionType> list = new List<AdditionType>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary element
                    AdditionType element = new AdditionType();
                    //Write the information in the temporary element
                    element.Id = Convert.ToInt32(dataReader["ID"]);
                    element.Type = Convert.ToString(dataReader["type"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public List<AdditionType> GetAllAdditionType()
        {
            string query = "SELECT * FROM additiontype";
            List<AdditionType> list = new List<AdditionType>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary element
                    AdditionType element = new AdditionType();
                    //Write the information in the temporary element
                    element.Id = Convert.ToInt32(dataReader["ID"]);
                    element.Type = Convert.ToString(dataReader["type"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public int CountAdditionType()
        {
            string query = "SELECT Count(*) FROM additiontype";
            int Count = -1;

            //Open Connection
            if (this.connectionOpen == true)
            {
                //Create Mysql Command
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //ExecuteScalar will return one value
                Count = int.Parse(cmd.ExecuteScalar() + "");

                return Count;
            }
            else
            {
                return Count;
            }
        }
        public void UpdateAdditionType(int id, AdditionType element)
        {
            string query = "UPDATE additiontype SET type='" + element.Type + "' WHERE ID='" + id + "';";
            SendQuery(query);
        }
        #endregion

        #region CustomQueries
        public int InsertIntoCustomQueries(List<CustomQueriesDB> list)
        {
            string query;
            bool first = true;

            query = "INSERT INTO customqueries (query, type) VALUES ";

            foreach (CustomQueriesDB element in list)
            {
                if (first)
                {
                    first = false;
                }
                else
                {
                    query += ",";
                }
                query += "('" + element.Query + "', '" + element.Type + "')";
            }

            query += ";";

            int lastId = SendQueryRet(query);
            return lastId;
        }
        public void DeleteAllCustomQueries()
        {
            string query = "DELETE FROM customqueries;";
            SendQuery(query);
        }
        public void DeleteRangeCustomQueries(int startID, int endID)
        {
            string query = " DELETE FROM customqueries WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            SendQuery(query);
        }
        public List<CustomQueriesDB> GetRangeCustomQueries(int startID, int endID)
        {
            string query = "SELECT * FROM customqueries WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            List<CustomQueriesDB> list = new List<CustomQueriesDB>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Addition element
                    CustomQueriesDB element = new CustomQueriesDB();
                    //Write the information in the temporary element
                    element.Id = Convert.ToInt32(dataReader["ID"]);
                    element.Query = Convert.ToString(dataReader["query"]);
                    element.Type = Convert.ToString(dataReader["type"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public List<CustomQueriesDB> GetAllCustomQueries()
        {
            string query = "SELECT * FROM customqueries";
            List<CustomQueriesDB> list = new List<CustomQueriesDB>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Addition element
                    CustomQueriesDB element = new CustomQueriesDB();
                    //Write the information in the temporary element
                    element.Id = Convert.ToInt32(dataReader["ID"]);
                    element.Query = Convert.ToString(dataReader["query"]);
                    element.Type = Convert.ToString(dataReader["type"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                if (this.ExistsValue("OutOfCustomSync"))
                {
                    this.UpdateConfig("OutOfCustomSync", 0x00);
                }
                else
                {
                    this.AddValue("OutOfCustomSync", 0x00);
                }

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public int CountCustomQueries()
        {
            string query = "SELECT Count(*) FROM customqueries";
            int Count = -1;

            //Open Connection
            if (this.connectionOpen == true)
            {
                //Create Mysql Command
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //ExecuteScalar will return one value
                Count = int.Parse(cmd.ExecuteScalar() + "");

                return Count;
            }
            else
            {
                return Count;
            }
        }
        public void UpdateCustomQueries(int id, CustomQueriesDB element)
        {
            string query = "UPDATE customqueries SET query='" + element.Query + "', type='" + element.Type + "';";
            SendQuery(query);
        }
        #endregion

        #region Device
        public int InsertIntoDevice(List<DeviceDB> list)
        {
            string query;
            bool first = true;

            query = "INSERT INTO device (note, name, purchaseDate, warranty, manufacturer, inventoryNumber, serialNumber, changeFlag, deviceTypeId, suppliereId) VALUES ";

            foreach (DeviceDB element in list)
            {
                if (first)
                {
                    first = false;
                }
                else
                {
                    query += ",";
                }
                query += "('" + element.Note + "', '" + element.Name + "', '" + element.PurchaseDate.Year + "-" + element.PurchaseDate.Month + "-" + element.PurchaseDate.Day + "', " + element.Warranty + "', '" + element.Manufacturer + "', '" + element.InventoryNumber + "', '" + element.SerialNumber + "', '" + "', '" + element.ChangeFlag + "', '" + element.DeviceTypeId + "', '" + element.SupplierId + "')";
            }

            query += ";";

            int lastId = SendQueryRet(query);
            return lastId;
        }
        public int InsertIntoDevice(DeviceDB element)
        {
            string query;

            query = "INSERT INTO device (note, name, purchaseDate, warranty, manufacturer, inventoryNumber, serialNumber, changeFlag, deviceType_Id, supplier_Id) VALUES ";

            query += "('" + element.Note + "', '" + element.Name + "', '" + element.PurchaseDate.Year + "-" + element.PurchaseDate.Month + "-" + element.PurchaseDate.Day + "', '" + element.Warranty + "', '" + element.Manufacturer + "', '" + element.InventoryNumber + "', '" + element.SerialNumber + "', '" + element.ChangeFlag + "', '" + element.DeviceTypeId + "', '" + element.SupplierId + "')";


            query += ";";
            int lastId = SendQueryRet(query);
            return lastId;
        }

        public void DeleteAllDevice()
        {
            string query = "DELETE FROM device;";
            SendQuery(query);
        }
        public void DeleteRangeDevice(int startID, int endID)
        {
            string query = " DELETE FROM device WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            SendQuery(query);
        }
        public List<DeviceDB> GetRangeDevice(int startID, int endID)
        {
            string query = "SELECT * FROM device WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            List<DeviceDB> list = new List<DeviceDB>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Addition element
                    DeviceDB element = new DeviceDB();
                    //Write the information in the temporary element
                    element.Id = Convert.ToInt32(dataReader["ID"]);
                    element.Note = Convert.ToString(dataReader["note"]);
                    element.Name = Convert.ToString(dataReader["name"]);
                    element.PurchaseDate = Convert.ToDateTime(dataReader["purchaseDate"]);
                    element.Warranty = Convert.ToInt32(dataReader["warranty"]);
                    element.Manufacturer = Convert.ToString(dataReader["manufacturer"]);
                    element.InventoryNumber = Convert.ToString(dataReader["inventoryNumber"]);
                    element.SerialNumber = Convert.ToString(dataReader["serialNumber"]);
                    element.ChangeFlag = Convert.ToInt32(dataReader["changeFlag"]);
                    element.DeviceTypeId = Convert.ToInt32(dataReader["deviceType_ID"]);
                    element.SupplierId = Convert.ToInt32(dataReader["supplier_ID"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public List<DeviceDB> GetAllDevice()
        {
            string query = "SELECT * FROM device";
            List<DeviceDB> list = new List<DeviceDB>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Addition element
                    DeviceDB element = new DeviceDB();
                    //Write the information in the temporary element
                    element.Id = Convert.ToInt32(dataReader["ID"]);
                    element.Note = Convert.ToString(dataReader["note"]);
                    element.Name = Convert.ToString(dataReader["name"]);
                    element.PurchaseDate = Convert.ToDateTime(dataReader["purchaseDate"]);
                    element.Warranty = Convert.ToInt32(dataReader["warranty"]);
                    element.Manufacturer = Convert.ToString(dataReader["manufacturer"]);
                    element.InventoryNumber = Convert.ToString(dataReader["inventoryNumber"]);
                    element.SerialNumber = Convert.ToString(dataReader["serialNumber"]);
                    element.ChangeFlag = Convert.ToInt32(dataReader["changeFlag"]);
                    element.DeviceTypeId = Convert.ToInt32(dataReader["deviceType_ID"]);
                    element.SupplierId = Convert.ToInt32(dataReader["supplier_ID"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public int CountCustomDevice()
        {
            string query = "SELECT Count(*) FROM device";
            int Count = -1;

            //Open Connection
            if (this.connectionOpen == true)
            {
                //Create Mysql Command
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //ExecuteScalar will return one value
                Count = int.Parse(cmd.ExecuteScalar() + "");

                return Count;
            }
            else
            {
                return Count;
            }
        }
        public void UpdateDevice(int id, DeviceDB element)
        {
            string query = "UPDATE device SET note='" + element.Note + "', name='" + element.Name + "', purchaseDate='" + element.PurchaseDate.Year + "-" + element.PurchaseDate.Month + "-" + element.PurchaseDate.Day + "', warranty='" + element.Warranty + "', manufacturer='" + element.Manufacturer + "', inventoryNumber='" + element.InventoryNumber + "', serialNumber='" + element.SerialNumber + "', changeFlag='" + element.ChangeFlag + "', deviceType_Id='" + element.DeviceTypeId + "', supplier_Id='" + element.SupplierId + "' WHERE ID='" + id + "';";
            
            SendQuery(query);
        }
        #endregion

        #region DeviceType
        public int InsertIntoDeviceType(List<DeviceType> list)
        {
            string query;
            bool first = true;

            query = "INSERT INTO deviceType (description) VALUES ";

            foreach (DeviceType deviceType in list)
            {
                if (first)
                {
                    first = false;
                }
                else
                {
                    query += ",";
                }
                query += "('" + deviceType.Description + "')";
            }

            query += ";";

            int lastId = SendQueryRet(query);
            return lastId;
        }
        public int InsertIntoDeviceType(DeviceType deviceType)
        {
            string query;

            query = "INSERT INTO deviceType (description) VALUES ";

                query += "('" + deviceType.Description + "')";
            

            query += ";";

            int lastId = SendQueryRet(query);
            return lastId;
        }
        public void DeleteAllDeviceType()
        {
            string query = "DELETE FROM deviceType;";
            SendQuery(query);
        }
        public void DeleteRangeDeviceType(int startID, int endID)
        {
            string query = " DELETE FROM deviceType WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            SendQuery(query);
        }
        public List<DeviceType> GetRangeDeviceType(int startID, int endID)
        {
            string query = "SELECT * FROM deviceType WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            List<DeviceType> list = new List<DeviceType>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary DeviceType element
                    DeviceType element = new DeviceType();
                    //Write the information in the temporary element
                    element.Id = Convert.ToInt32(dataReader["ID"]);
                    element.Description = Convert.ToString(dataReader["description"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public List<DeviceType> GetAllDeviceType()
        {
            string query = "SELECT * FROM deviceType";
            List<DeviceType> list = new List<DeviceType>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary DeviceType element
                    DeviceType element = new DeviceType();
                    //Write the information in the temporary element
                    element.Id = Convert.ToInt32(dataReader["ID"]);
                    element.Description = Convert.ToString(dataReader["description"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public int CountDeviceType()
        {
            string query = "SELECT Count(*) FROM deviceType";
            int Count = -1;

            //Open Connection
            if (this.connectionOpen == true)
            {
                //Create Mysql Command
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //ExecuteScalar will return one value
                Count = int.Parse(cmd.ExecuteScalar() + "");

                return Count;
            }
            else
            {
                return Count;
            }
        }
        public void UpdateDeviceType(int id, DeviceType element)
        {
            string query = "UPDATE deviceType SET description='" + element.Description + "' WHERE ID='" + id + "';";
            SendQuery(query);
        }
        #endregion

        #region Mac
        public int InsertIntoMac(List<Mac> list)
        {
            string query;
            bool first = true;

            query = "INSERT INTO mac (switchPort_ID, macAddress, device_ID) VALUES ";

            foreach (Mac element in list)
            {
                if (first)
                {
                    first = false;
                }
                else
                {
                    query += ",";
                }
                query += "('" + element.SwitchPortId + "', '" + element.MacAdress + "', '" + element.DeviceId + "')";
            }

            query += ";";

            int lastId = SendQueryRet(query);
            return lastId;
        }
        public int InsertIntoMac(Mac element)
        {
            string query;

            query = "INSERT INTO mac (switchPort_ID, macAddress, device_ID) VALUES ";

            query += "('" + element.SwitchPortId + "', '" + element.MacAdress + "', '" + element.DeviceId + "')";


            query += ";";

            int lastId = SendQueryRet(query);
            return lastId;
        }
        public void DeleteAllMac()
        {
            string query = "DELETE FROM mac;";
            SendQuery(query);
        }
        public void DeleteRangeMac(int startID, int endID)
        {
            string query = " DELETE FROM mac WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            SendQuery(query);
        }
        public List<Mac> GetRangeMac(int startID, int endID)
        {
            string query = "SELECT * FROM mac WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            List<Mac> list = new List<Mac>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Mac element
                    Mac element = new Mac();
                    //Write the information in the temporary element
                    element.Id = Convert.ToInt32(dataReader["ID"]);
                    element.SwitchPortId = Convert.ToInt32(dataReader["switchPort_ID"]);
                    element.MacAdress = Convert.ToString(dataReader["macAddress"]);
                    element.DeviceId = Convert.ToInt32(dataReader["device_ID"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public List<Mac> GetAllMac()
        {
            string query = "SELECT * FROM mac";
            List<Mac> elements = new List<Mac>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Mac element
                    Mac element = new Mac();
                    //Write the information in the temporary element
                    element.Id = Convert.ToInt32(dataReader["ID"]);
                    element.SwitchPortId = Convert.ToInt32(dataReader["switchPort_ID"]);
                    element.MacAdress = Convert.ToString(dataReader["macAddress"]);
                    element.DeviceId = Convert.ToInt32(dataReader["device_ID"]);
                    //insert the temporary element into the list
                    elements.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return elements;
            }
            else
            {
                return elements;
            }
        }
        public int CountMac()
        {
            string query = "SELECT Count(*) FROM mac";
            int Count = -1;

            //Open Connection
            if (this.connectionOpen == true)
            {
                //Create Mysql Command
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //ExecuteScalar will return one value
                Count = int.Parse(cmd.ExecuteScalar() + "");

                return Count;
            }
            else
            {
                return Count;
            }
        }
        public void UpdateMac(int id, Mac element)
        {
            string query = "UPDATE mac SET switchPort_ID='" + element.SwitchPortId + "', macAddress='" + element.MacAdress + "', device_ID='" + element.DeviceId + "' WHERE ID='" + id + "';";
            SendQuery(query);
        }
        #endregion

        #region PatchPanel
        public int InsertIntoPatchPanel(List<PatchPanel> list)
        {
            string query;
            bool first = true;

            query = "INSERT INTO patchPanel (name, switchPort_ID, switchPort_switch_ID, room_ID) VALUES ";

            foreach (PatchPanel element in list)
            {
                if (first)
                {
                    first = false;
                }
                else
                {
                    query += ",";
                }
                query += "('" + element.Name + "', '" + element.SwitchPortId + "', '" + element.SwitchPortSwitchId + "', '" + element.RoomId + "')";
            }

            query += ";";

            int lastId = SendQueryRet(query);
            return lastId;
        }
        public void DeleteAllPatchPanel()
        {
            string query = "DELETE FROM patchPanel;";
            SendQuery(query);
        }
        public void DeleteRangePatchPanel(int startID, int endID)
        {
            string query = " DELETE FROM patchPanel WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            SendQuery(query);
        }
        public List<PatchPanel> GetRangePatchPanel(int startID, int endID)
        {
            string query = "SELECT * FROM patchPanel WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            List<PatchPanel> list = new List<PatchPanel>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Mac element
                    PatchPanel element = new PatchPanel();
                    //Write the information in the temporary element
                    element.Id = Convert.ToInt32(dataReader["ID"]);
                    element.Name = Convert.ToString(dataReader["name"]);
                    element.SwitchPortId = Convert.ToInt32(dataReader["switchPort_ID"]);
                    element.SwitchPortSwitchId = Convert.ToInt32(dataReader["switchPort_switch_ID"]);
                    element.RoomId = Convert.ToInt32(dataReader["room_ID"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public List<PatchPanel> GetAllPatchPanel()
        {
            string query = "SELECT * FROM patchPanel";
            List<PatchPanel> list = new List<PatchPanel>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Mac element
                    PatchPanel element = new PatchPanel();
                    //Write the information in the temporary element
                    element.Id = Convert.ToInt32(dataReader["ID"]);
                    element.Name = Convert.ToString(dataReader["name"]);
                    element.SwitchPortId = Convert.ToInt32(dataReader["switchPort_ID"]);
                    element.SwitchPortSwitchId = Convert.ToInt32(dataReader["switchPort_switch_ID"]);
                    element.RoomId = Convert.ToInt32(dataReader["room_ID"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public int CountPatchPanel()
        {
            string query = "SELECT Count(*) FROM patchPanel";
            int Count = -1;

            //Open Connection
            if (this.connectionOpen == true)
            {
                //Create Mysql Command
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //ExecuteScalar will return one value
                Count = int.Parse(cmd.ExecuteScalar() + "");

                return Count;
            }
            else
            {
                return Count;
            }
        }
        public void UpdatePatchPanel(int id, PatchPanel element)
        {
            string query = "UPDATE patchPanel SET name='" + element.Name +"'switchPort_ID='" + element.SwitchPortId + "', switchPort_switch_ID='" + element.SwitchPortSwitchId + "', room_ID='" + element.RoomId + "' WHERE ID='" + id + "';";
            SendQuery(query);
        }
        #endregion

        #region Relocation
        public int InsertIntoRelocation(List<Relocation> list)
        {
            string query;
            bool first = true;

            query = "INSERT INTO relocation (date, device_ID, roomIs_ID, roomShould_ID) VALUES ";

            foreach (Relocation element in list)
            {
                if (first)
                {
                    first = false;
                }
                else
                {
                    query += ",";
                }
                query += "('" + ConvertToDateTimeString(element.Date) + "', '" + element.DeviceId + "', '" + element.RoomIsId + "', '" + element.RoomShouldId + "')";
            }

            query += ";";

            int lastId = SendQueryRet(query);
            return lastId;
        }
        public int InsertIntoRelocation(Relocation element)
        {
            string query;

            query = "INSERT INTO relocation (date, device_ID, roomIs_ID, roomShould_ID) VALUES ";

            query += "('" + ConvertToDateTimeString(element.Date) + "', '" + element.DeviceId + "', '" + element.RoomIsId + "', '" + element.RoomShouldId + "');";
            

            int lastId = SendQueryRet(query);
            return lastId;
        }
        public void DeleteAllRelocation()
        {
            string query = "DELETE FROM relocation;";
            SendQuery(query);
        }
        public void DeleteRangeRelocation(int startID, int endID)
        {
            string query = " DELETE FROM relocation WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            SendQuery(query);
        }
        public List<Relocation> GetRangeRelocation(int startID, int endID)
        {
            string query = "SELECT * FROM relocation WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            List<Relocation> list = new List<Relocation>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Mac element
                    Relocation element = new Relocation();
                    //Write the information in the temporary element
                    element.Id = Convert.ToInt32(dataReader["ID"]);
                    element.Date = Convert.ToDateTime(dataReader["date"]);
                    element.DeviceId = Convert.ToInt32(dataReader["device_ID"]);
                    element.RoomIsId = Convert.ToInt32(dataReader["roomIs_ID"]);
                    element.RoomShouldId = Convert.ToInt32(dataReader["roomShould_ID"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public List<Relocation> GetAllRelocation()
        {
            string query = "SELECT * FROM relocation";
            List<Relocation> list = new List<Relocation>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Mac element
                    Relocation element = new Relocation();
                    //Write the information in the temporary element
                    element.Id = Convert.ToInt32(dataReader["ID"]);
                    element.Date = Convert.ToDateTime(dataReader["date"]);
                    element.DeviceId = Convert.ToInt32(dataReader["device_ID"]);
                    element.RoomIsId = Convert.ToInt32(dataReader["roomIs_ID"]);
                    element.RoomShouldId = Convert.ToInt32(dataReader["roomShould_ID"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public int CountRelocation()
        {
            string query = "SELECT Count(*) FROM relocation";
            int Count = -1;

            //Open Connection
            if (this.connectionOpen == true)
            {
                //Create Mysql Command
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //ExecuteScalar will return one value
                Count = int.Parse(cmd.ExecuteScalar() + "");

                return Count;
            }
            else
            {
                return Count;
            }
        }
        public void UpdatePatchPanel(int id, Relocation element)
        {
            string query = "UPDATE relocation SET date='" + ConvertToDateTimeString(element.Date) + "'device_ID='" + element.DeviceId + "', roomIs_ID='" + element.RoomIsId + "', roomShould_ID='" + element.RoomShouldId + "' WHERE ID='" + id + "';";
            SendQuery(query);
        }
        #endregion

        #region Room
        public int InsertIntoRoom(List<Room> list)
        {
            string query;
            bool first = true;

            query = "INSERT INTO room (name, number) VALUES ";

            foreach (Room element in list)
            {
                if (first)
                {
                    first = false;
                }
                else
                {
                    query += ",";
                }
                query += "('" + element.Name + "', '" + element.Number + "')";
            }

            query += ";";

            int lastId = SendQueryRet(query);
            return lastId;
        }
        public void DeleteAllRoom()
        {
            string query = "DELETE FROM room;";
            SendQuery(query);
        }
        public void DeleteRangeRoom(int startID, int endID)
        {
            string query = " DELETE FROM room WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            SendQuery(query);
        }
        public List<Room> GetRangeRoom(int startID, int endID)
        {
            string query = "SELECT * FROM room WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            List<Room> list = new List<Room>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Mac element
                    Room element = new Room();
                    //Write the information in the temporary element
                    element.Id = Convert.ToInt32(dataReader["ID"]);
                    element.Name = Convert.ToString(dataReader["name"]);
                    element.Number = Convert.ToString(dataReader["number"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public List<Room> GetAllRoom()
        {
            string query = "SELECT * FROM room";
            List<Room> list = new List<Room>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Mac element
                    Room element = new Room();
                    //Write the information in the temporary element
                    element.Id = Convert.ToInt32(dataReader["ID"]);
                    element.Name = Convert.ToString(dataReader["name"]);
                    element.Number = Convert.ToString(dataReader["number"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public int CountRoom()
        {
            string query = "SELECT Count(*) FROM room";
            int Count = -1;

            //Open Connection
            if (this.connectionOpen == true)
            {
                //Create Mysql Command
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //ExecuteScalar will return one value
                Count = int.Parse(cmd.ExecuteScalar() + "");

                return Count;
            }
            else
            {
                return Count;
            }
        }
        public void UpdateRoom(int id, Room element)
        {
            string query = "UPDATE room SET name='" + element.Name + "'number='" + element.Number + "' WHERE ID='" + id + "';";
            SendQuery(query);
        }
        #endregion

        #region Supplier
        public int InsertIntoSupplier(List<Supplier> list)
        {
            string query;
            bool first = true;

            query = "INSERT INTO supplier (name, contactService, contactSalesOffice, note) VALUES ";

            foreach (Supplier element in list)
            {
                if (first)
                {
                    first = false;
                }
                else
                {
                    query += ",";
                }
                query += "('" + element.Name + "', '" + element.ContactService + "', '" + element.ContactSalesOffice + "', '" + element.Note + "')";
            }

            query += ";";

            int lastId = SendQueryRet(query);
            return lastId;
        }
        public int InsertIntoSupplier(Supplier element)
        {
            string query;

            query = "INSERT INTO supplier (name, contactService, contactSalesOffice, note) VALUES ";

            query += "('" + element.Name + "', '" + element.ContactService + "', '" + element.ContactSalesOffice + "', '" + element.Note + "');";
            
            int lastId = SendQueryRet(query);
            return lastId;
        }
        public void DeleteAllSupplier()
        {
            string query = "DELETE FROM supplier;";
            SendQuery(query);
        }
        public void DeleteRangeSupplier(int startID, int endID)
        {
            string query = " DELETE FROM supplier WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            SendQuery(query);
        }
        public List<Supplier> GetRangeSupplier(int startID, int endID)
        {
            string query = "SELECT * FROM supplier WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            List<Supplier> list = new List<Supplier>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Mac element
                    Supplier element = new Supplier();
                    //Write the information in the temporary element
                    element.Id = Convert.ToInt32(dataReader["ID"]);
                    element.Name = Convert.ToString(dataReader["name"]);
                    element.ContactService = Convert.ToString(dataReader["contactService"]);
                    element.ContactSalesOffice = Convert.ToString(dataReader["contactSalesOffice"]);
                    element.Note = Convert.ToString(dataReader["note"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public List<Supplier> GetAllSupplier()
        {
            string query = "SELECT * FROM supplier";
            List<Supplier> list = new List<Supplier>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Mac element
                    Supplier element = new Supplier();
                    //Write the information in the temporary element
                    element.Id = Convert.ToInt32(dataReader["ID"]);
                    element.Name = Convert.ToString(dataReader["name"]);
                    element.ContactService = Convert.ToString(dataReader["contactService"]);
                    element.ContactSalesOffice = Convert.ToString(dataReader["contactSalesOffice"]);
                    element.Note = Convert.ToString(dataReader["note"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public int CountSupplier()
        {
            string query = "SELECT Count(*) FROM supplier";
            int Count = -1;

            //Open Connection
            if (this.connectionOpen == true)
            {
                //Create Mysql Command
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //ExecuteScalar will return one value
                Count = int.Parse(cmd.ExecuteScalar() + "");

                return Count;
            }
            else
            {
                return Count;
            }
        }
        public void UpdateSupplier(int id, Supplier element)
        {
            string query = "UPDATE supplier SET name='" + element.Name + "',contactService='" + element.ContactService + "', contactSalesOffice='" + element.ContactSalesOffice + "', note='" + element.Note + "' WHERE ID='" + id + "';";
            SendQuery(query);
        }
        public int GetSupplierIdByName(string name)
        {
            int retValue = -1;
            string query = "SELECT * from supplier WHERE name LIKE '" + name + "'";
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                if (dataReader.Read())
                {
                    //return ID
                    retValue = (int)dataReader["ID"];
                }

                //close Data Reader
                dataReader.Close();
            }
            return retValue;
        }
        #endregion

        #region Switch
        public int InsertIntoSwitch(List<SwitchDB> list)
        {
            string query;
            bool first = true;

            query = "INSERT INTO switch (name, ip) VALUES ";

            foreach (SwitchDB element in list)
            {
                if (first)
                {
                    first = false;
                }
                else
                {
                    query += ",";
                }
                query += "('" + element.Name + "', '" + element.Ip + "','" + element.ChangeFlag + "','" + element.Note + ")";
            }

            query += ";";

            int lastId = SendQueryRet(query);
            return lastId;
        }
        public void DeleteAllSwitch()
        {
            string query = "DELETE FROM switch;";
            SendQuery(query);
        }
        public void DeleteRangeSwitch(int startID, int endID)
        {
            string query = " DELETE FROM switch WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            SendQuery(query);
        }
        public List<SwitchDB> GetRangeSwitch(int startID, int endID)
        {
            string query = "SELECT * FROM switch WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            List<SwitchDB> list = new List<SwitchDB>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Mac element
                    SwitchDB element = new SwitchDB();
                    //Write the information in the temporary element
                    element.Id = Convert.ToInt32(dataReader["ID"]);
                    element.Name = Convert.ToString(dataReader["name"]);
                    element.Ip = Convert.ToString(dataReader["ip"]);
                    element.ChangeFlag = Convert.ToInt32(dataReader["changeFlag"]);
                    element.Note = Convert.ToString(dataReader["note"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public List<SwitchDB> GetAllSwitch(bool updateFlag)
        {
            string query = "SELECT * FROM switch";
            List<SwitchDB> list = new List<SwitchDB>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Mac element
                    SwitchDB element = new SwitchDB();
                    //Write the information in the temporary element
                    element.Id = Convert.ToInt32(dataReader["ID"]);
                    element.Name = Convert.ToString(dataReader["name"]);
                    element.Ip = Convert.ToString(dataReader["ip"]);
                    element.ChangeFlag = Convert.ToInt32(dataReader["changeFlag"]);
                    element.Note = Convert.ToString(dataReader["note"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                if (updateFlag)
                {
                    if (this.ExistsValue("OutOfSwitchSync"))
                    {
                        this.UpdateConfig("OutOfSwitchSync", 0x00);
                    }
                    else
                    {
                        this.AddValue("OutOfSwitchSync", 0x00);
                    }
                }
                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public int CountSwitch()
        {
            string query = "SELECT Count(*) FROM switch";
            int Count = -1;

            //Open Connection
            if (this.connectionOpen == true)
            {
                //Create Mysql Command
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //ExecuteScalar will return one value
                Count = int.Parse(cmd.ExecuteScalar() + "");

                return Count;
            }
            else
            {
                return Count;
            }
        }
        public void UpdateSwitch(int id, SwitchDB element)
        {
            string query = "UPDATE switch SET name='" + element.Name + "', ip='" + element.Ip + "', changeFlag='" + element.ChangeFlag + "', note='" + element.Note + "' WHERE ID='" + id + "';";
            SendQuery(query);
        }
        #endregion

        #region SwitchHasVlan
        public int InsertIntoSwitchHasVlan(List<SwitchHasVlan> list)
        {
            string query;
            bool first = true;

            query = "INSERT INTO switch_has_vlan (switch_ID, vlan_ID) VALUES ";

            foreach (SwitchHasVlan element in list)
            {
                if (first)
                {
                    first = false;
                }
                else
                {
                    query += ",";
                }
                query += "('" + element.SwitchId + "', '" + element.VlanId + "')";
            }

            query += ";";

            int lastId = SendQueryRet(query);
            return lastId;
        }
        public void DeleteAllSwitchHasVlan()
        {
            string query = "DELETE FROM switch_has_vlan;";
            SendQuery(query);
        }
        public void DeleteRangeSwitchHasVlan(int startID, int endID)
        {
            string query = " DELETE FROM switch_has_vlan WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            SendQuery(query);
        }
        public List<SwitchHasVlan> GetRangeSwichHasVlan(int startID, int endID)
        {
            string query = "SELECT * FROM switch_has_vlan WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            List<SwitchHasVlan> list = new List<SwitchHasVlan>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Mac element
                    SwitchHasVlan element = new SwitchHasVlan();
                    //Write the information in the temporary element
                    element.SwitchId = Convert.ToInt32(dataReader["switch_ID"]);
                    element.VlanId = Convert.ToInt32(dataReader["vlan_ID"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public List<SwitchHasVlan> GetAllSwitchHasVlan()
        {
            string query = "SELECT * FROM switch_has_vlan";
            List<SwitchHasVlan> list = new List<SwitchHasVlan>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Mac element
                    SwitchHasVlan element = new SwitchHasVlan();
                    //Write the information in the temporary element
                    element.SwitchId = Convert.ToInt32(dataReader["switch_ID"]);
                    element.VlanId = Convert.ToInt32(dataReader["vlan_ID"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public int CountSwitchHasVlan()
        {
            string query = "SELECT Count(*) FROM switch_has_vlan";
            int Count = -1;

            //Open Connection
            if (this.connectionOpen == true)
            {
                //Create Mysql Command
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //ExecuteScalar will return one value
                Count = int.Parse(cmd.ExecuteScalar() + "");

                return Count;
            }
            else
            {
                return Count;
            }
        }
        public void UpdateSwitchHasVlan(int id, SwitchHasVlan element)
        {
            string query = "UPDATE switch_has_vlan SET switch_ID='" + element.SwitchId + "'vlan_ID='" + element.VlanId + "' WHERE ID='" + id + "';";
            SendQuery(query);
        }
        #endregion

        #region SwitchPort
        public int InsertIntoSwitchPort(List<SwitchPort> list)
        {
            string query;
            bool first = true;

            query = "INSERT INTO switchPort (name, speed, switch_ID) VALUES ";

            foreach (SwitchPort element in list)
            {
                if (first)
                {
                    first = false;
                }
                else
                {
                    query += ",";
                }
                query += "('" + element.Name + "', '" + element.Speed + "', '" + element.SwitchId + "')";
            }

            query += ";";

            int lastId = SendQueryRet(query);
            return lastId;
        }
        public int InsertIntoSwitchPort(SwitchPort element)
        {
            string query;

            query = "INSERT INTO switchPort (name, speed, switch_ID) VALUES ";

            query += "('" + element.Name + "', '" + element.Speed + "', '" + element.SwitchId + "')";


            query += ";";

            int lastId = SendQueryRet(query);
            return lastId;
        }
        public void DeleteAllSwitchPort()
        {
            string query = "DELETE FROM switchPort;";
            SendQuery(query);
        }
        public void DeleteRangeSwitchPort(int startID, int endID)
        {
            string query = " DELETE FROM switchPort WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            SendQuery(query);
        }
        public List<SwitchPort> GetRangeSwitchPort(int startID, int endID)
        {
            string query = "SELECT * FROM switchPort WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            List<SwitchPort> list = new List<SwitchPort>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Mac element
                    SwitchPort element = new SwitchPort();
                    //Write the information in the temporary element
                    element.Id = Convert.ToInt32(dataReader["ID"]);
                    element.Name = Convert.ToString(dataReader["name"]);
                    element.Speed = Convert.ToString(dataReader["speed"]);
                    element.SwitchId = Convert.ToInt32(dataReader["switch_ID"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public List<SwitchPort> GetAllSwitchPort()
        {
            string query = "SELECT * FROM switchPort";
            List<SwitchPort> list = new List<SwitchPort>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Mac element
                    SwitchPort element = new SwitchPort();
                    //Write the information in the temporary element
                    element.Id = Convert.ToInt32(dataReader["ID"]);
                    element.Name = Convert.ToString(dataReader["name"]);
                    element.Speed = Convert.ToString(dataReader["speed"]);
                    element.SwitchId = Convert.ToInt32(dataReader["switch_ID"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public int CountSwitchPort()
        {
            string query = "SELECT Count(*) FROM switchPort";
            int Count = -1;

            //Open Connection
            if (this.connectionOpen == true)
            {
                //Create Mysql Command
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //ExecuteScalar will return one value
                Count = int.Parse(cmd.ExecuteScalar() + "");

                return Count;
            }
            else
            {
                return Count;
            }
        }
        public void UpdateSwitchPort(int id, SwitchPort element)
        {
            string query = "UPDATE switchPort SET name='" + element.Name + "', speed='" + element.Speed + "', switch_ID='" + element.SwitchId + "', changeFlag='"+element.ChangeFlag+"' WHERE ID='" + id + "';";
            SendQuery(query);
        }
        #endregion

        #region User
        public int InsertIntoUser(List<User> list)
        {
            string query;
            bool first = true;

            query = "INSERT INTO user (name, password, rights) VALUES ";

            foreach (User element in list)
            {
                if (first)
                {
                    first = false;
                }
                else
                {
                    query += ",";
                }
                query += "('" + element.Name + "', '" + element.Password + "', '" + element.Rights + "')";
            }

            query += ";";

            int lastId = SendQueryRet(query);
            return lastId;
        }
        public void DeleteAllUser()
        {
            string query = "DELETE FROM user;";
            SendQuery(query);
        }
        public void DeleteRangeUser(int startID, int endID)
        {
            string query = " DELETE FROM user WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            SendQuery(query);
        }
        public List<User> GetRangeUser(int startID, int endID)
        {
            string query = "SELECT * FROM user WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            List<User> list = new List<User>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Mac element
                    User element = new User();
                    //Write the information in the temporary element
                    element.Id = Convert.ToInt32(dataReader["ID"]);
                    element.Name = Convert.ToString(dataReader["name"]);
                    element.Password = Convert.ToString(dataReader["password"]);
                    element.Rights = Convert.ToString(dataReader["rights"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public List<User> GetAllUser()
        {
            string query = "SELECT * FROM user";
            List<User> list = new List<User>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Mac element
                    User element = new User();
                    //Write the information in the temporary element
                    element.Id = Convert.ToInt32(dataReader["ID"]);
                    element.Name = Convert.ToString(dataReader["name"]);
                    element.Password = Convert.ToString(dataReader["password"]);
                    element.Rights = Convert.ToString(dataReader["rights"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public int CountUser()
        {
            string query = "SELECT Count(*) FROM user";
            int Count = -1;

            //Open Connection
            if (this.connectionOpen == true)
            {
                //Create Mysql Command
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //ExecuteScalar will return one value
                Count = int.Parse(cmd.ExecuteScalar() + "");

                return Count;
            }
            else
            {
                return Count;
            }
        }
        public void UpdateUser(int id, User element)
        {
            string query = "UPDATE user SET name='" + element.Name + "'password='" + element.Password + "', rights='" + element.Rights + "' WHERE ID='" + id + "';";
            SendQuery(query);
        }
        #endregion

        #region Vlan
        public int InsertIntoVlan(List<Vlan> list)
        {
            string query;
            bool first = true;

            query = "INSERT INTO vlan (name, vlanNumber) VALUES ";

            foreach (Vlan element in list)
            {
                if (first)
                {
                    first = false;
                }
                else
                {
                    query += ",";
                }
                query += "('" + element.Name + "', '" + element.VlanNumber + "')";
            }

            query += ";";

            int lastId = SendQueryRet(query);
            return lastId;
        }
        public void DeleteAllVlan()
        {
            string query = "DELETE FROM vlan;";
            SendQuery(query);
        }
        public void DeleteRangeVlan(int startID, int endID)
        {
            string query = " DELETE FROM vlan WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            SendQuery(query);
        }
        public List<Vlan> GetRangeVlan(int startID, int endID)
        {
            string query = "SELECT * FROM vlan WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            List<Vlan> list = new List<Vlan>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Mac element
                    Vlan element = new Vlan();
                    //Write the information in the temporary element
                    element.Id = Convert.ToInt32(dataReader["ID"]);
                    element.Name = Convert.ToString(dataReader["name"]);
                    element.VlanNumber = Convert.ToInt32(dataReader["vlanNumber"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public List<Vlan> GetAllVlan()
        {
            string query = "SELECT * FROM vlan";
            List<Vlan> list = new List<Vlan>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Mac element
                    Vlan element = new Vlan();
                    //Write the information in the temporary element
                    element.Id = Convert.ToInt32(dataReader["ID"]);
                    element.Name = Convert.ToString(dataReader["name"]);
                    element.VlanNumber = Convert.ToInt32(dataReader["vlanNumber"]);
                    //insert the temporary element into the list
                    list.Add(element);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public int CountVlan()
        {
            string query = "SELECT Count(*) FROM vlan";
            int Count = -1;

            //Open Connection
            if (this.connectionOpen == true)
            {
                //Create Mysql Command
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //ExecuteScalar will return one value
                Count = int.Parse(cmd.ExecuteScalar() + "");

                return Count;
            }
            else
            {
                return Count;
            }
        }
        public void UpdateVlan(int id, Vlan element)
        {
            string query = "UPDATE vlan SET name='" + element.Name + "', vlanNumber='" + element.VlanNumber + "' WHERE ID='" + id + "';";
            SendQuery(query);
        }
        #endregion

        #region Flag
        public int InsertIntoFlag(int flag, string name, DateTime date)
        {
            string query;
            int lastId = 0;
            if (this.connectionOpen == true)
            {
                query = "INSERT INTO flag (flag,name,date) VALUES ('" + flag + "', '" + name + "', '" + ConvertToDateTimeString(date) + "');";

                lastId = SendQueryRet(query);
            }
            return lastId;
        } //only one flag can be inserted at a time
        public void DeleteAllFlag()
        {
            string query = "DELETE FROM flag;";
            SendQuery(query);
        }
        public void DeleteRangeFlag(int startID, int endID)
        {
            string query = " DELETE FROM flag WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            SendQuery(query);
        }
        public List<int> GetRangeFlag(int startID, int endID)
        {
            string query = "SELECT * FROM flag WHERE ID BETWEEN " + startID + " AND " + endID + ";";
            List<int> list = new List<int>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Mac element
                    int flag;
                    //Write the information in the temporary element
                    flag = Convert.ToInt32(dataReader["flag"]);
                    //insert the temporary element into the list
                    list.Add(flag);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public List<int> GetAllFlag()
        {
            string query = "SELECT * FROM flag";
            List<int> list = new List<int>();

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Create a temporary Mac element
                    int flag;
                    //Write the information in the temporary element
                    flag = Convert.ToInt32(dataReader["flag"]);
                    //insert the temporary element into the list
                    list.Add(flag);
                }

                //close Data Reader
                dataReader.Close();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public int CountFlag()
        {
            string query = "SELECT Count(*) FROM flag";
            int Count = -1;

            //Open Connection
            if (this.connectionOpen == true)
            {
                //Create Mysql Command
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //ExecuteScalar will return one value
                Count = int.Parse(cmd.ExecuteScalar() + "");

                return Count;
            }
            else
            {
                return Count;
            }
        }
        public void UpdateFlag(int id, int flag)
        {
            string query = "UPDATE flag SET flag='" + flag + "' WHERE ID='" + id + "';";
            SendQuery(query);
        }
        public int GetFlagIdByName(string name)
        {
            string query = "SELECT * from flag WHERE name LIKE '" + name + "'";
            int retID = -1;
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                if (dataReader.Read())
                {
                    //return ID
                    retID=(int)dataReader["ID"];
                }

                //close Data Reader
                dataReader.Close();
            }
            return retID;
        }
        public int GetFlag(int flagID)
        {
            string query = "SELECT * from flag WHERE ID='" + flagID + "';";
            int retFlag = 0;
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                if (dataReader.Read())
                {
                    //return flag
                    retFlag = (int)dataReader["flag"];
                }

                //close Data Reader
                dataReader.Close();
            }
            return retFlag;
        }
        #endregion

        #region Configuration
        public bool ExistsValue(string value)
        {
            bool PathExists = false;
            string query = "SELECT name FROM configuration WHERE name='"+value+"'";

            //Create Command
            MySqlCommand cmd = new MySqlCommand(query, connection);
            //Create a data reader and Execute the command
            MySqlDataReader dataReader = cmd.ExecuteReader();
            //counter

            //Read the data and store them in the list
            if(dataReader.Read())
            {
                PathExists = true;
            }
            dataReader.Close();
            return PathExists;
        }
        public int AddValue(string name, object value)
        {
            string insertion = value.ToString().Replace('\\', '/');

            if (this.connectionOpen == true)
            {
                string query;
                query = "INSERT INTO configuration (name,value) VALUES ('" + name + "', '" + insertion + "');";

                int lastId = SendQueryRet(query);
                return lastId;
            }
            return 0;
        }
        public void UpdateConfig(string name, object value)
        {
            string insertion = value.ToString().Replace('\\', '/');

            string query = "UPDATE configuration SET value='" + insertion + "' WHERE name='" + name + "';";
            SendQuery(query);
        }
        public Configuration GetAllConfiguration()
        {
            string query = "SELECT * FROM configuration";
            Configuration element = new Configuration();
            element.Debug = false;

            //Open connection
            if (this.connectionOpen == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();
                List<string> DhcpNetAddresses = new List<string>();
                //counter


                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    //Write the information in the temporary element
                    switch ((string)dataReader["name"])
                    {
                        case "MaxThreads":
                            element.MaxThreads = Convert.ToInt32(dataReader["value"]);
                            break;
                        case "Timeout":
                            element.Timeout = Convert.ToInt32(dataReader["value"]);
                            break;
                        case "DHCPNetAddress":
                            DhcpNetAddresses.Add((string)dataReader["value"]);
                            break;
                        case "DHCPAddress":
                            element.IPDHCP = (string)dataReader["value"];
                            break;
                        case "ScanTime":
                            element.ScanTime = Convert.ToDateTime(dataReader["value"]);
                            break;
                        case "Debug":
                            element.Debug = Convert.ToBoolean(dataReader["value"]);
                            break;
                    }
                    //insert the temporary element into the list
                }
                element.IPNetDHCP = DhcpNetAddresses.ToArray();

                //close Data Reader
                dataReader.Close();
                if (this.ExistsValue("OutOfConfigSync"))
                {
                    this.UpdateConfig("OutOfConfigSync", 0x00);
                }
                else
                {
                    this.AddValue("OutOfConfigSync", 0x00);
                }
            }

            return element;
        }
        #endregion
        
        private string ConvertToDateTimeString(DateTime time)
        {
            string ret = "";
            ret += time.Year + "-";
            ret += time.Month + "-";
            ret += time.Day + " ";
            ret += time.Hour + ":";
            ret += time.Minute + ":";
            ret += time.Second;

            return ret;
        }

    }
}